{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\linearmodels\\panel\\data.py:10: FutureWarning: The Panel class is removed from pandas. Accessing it from the top-level namespace will also be removed in the next version\n",
      "  from pandas import (Categorical, DataFrame, Index, MultiIndex, Panel, Series,\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd             # data package\n",
    "import matplotlib.pyplot as plt # graphics \n",
    "import datetime as dt\n",
    "import numpy as np\n",
    "\n",
    "import requests, io             # internet and input tools  \n",
    "import zipfile as zf            # zip file tools \n",
    "import os  \n",
    "\n",
    "import pyarrow as pa\n",
    "import pyarrow.parquet as pq\n",
    "\n",
    "import statsmodels.formula.api as smf\n",
    "from linearmodels.iv import IV2SLS\n",
    "from linearmodels.panel import PanelOLS\n",
    "\n",
    "from census import Census # This is new..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overview. \n",
    "\n",
    "This file essentially grabs the month by county files from the [Quarterly Census of Employment and Wages](https://www.bls.gov/cew/) files from the BLS and then creates employment measures at the county-level, monthly frequency. \n",
    "\n",
    "A comment about the data. This file uses the ``County High-Level`` file. The layouts are [here](https://data.bls.gov/cew/doc/layouts/cty_high_level_layout.htm). In this file, there are only supersectors, not at the finer NAICS level. To get stuff at the finer NAICS level, then one needs to use the single files (which are large about 500mb for each qurter)\n",
    "\n",
    "A couple of comments about the code:\n",
    "\n",
    "   - In the funciton ``clean_bls_quarter`` it has a set of catagories (NAICS or NAICS supersectors) and then will go through the file and grab the appropriate employment statistic. This later part is performed by the function ``bls_quarter_cat``\n",
    "    \n",
    "    \n",
    "   - It now goes from 2014 data (and further back if modified) and up to 2019 (which only have Q1 values. See the relese calander when updates will be made.\n",
    "   \n",
    "   - I'm using the super sector definitions from the bls here [https://www.bls.gov/iag/tgs/iag_index_naics.htm](https://www.bls.gov/iag/tgs/iag_index_naics.htm) Now what I do is pull out construction from the goods producing supersector, this just is pulling out super catagory 1012 in the stuff below. \n",
    "   \n",
    "### Step 1\n",
    "\n",
    "Bring in the trade/tariff data for which we will merge stuff...."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cwd = os.getcwd()\n",
    "\n",
    "trade_data = pq.read_table(cwd + \"\\\\data\\\\total_trade_data_2015.parquet\").to_pandas()\n",
    "\n",
    "trade_data[\"time\"] = pd.to_datetime(trade_data.time)\n",
    "\n",
    "trade_data.set_index([\"area_fips\", \"time\"],inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.4691811550724125"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_data.head()\n",
    "\n",
    "exposure = pd.qcut(trade_data.xs('2018-12-1', level=1).tariff, 4 ,labels = False)\n",
    "\n",
    "most_exposed = exposure[exposure == 3].index.tolist()\n",
    "\n",
    "trade_data.loc[most_exposed].xs('2018-12-1', level=1).tariff.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most exposed guys should always be about 6.46. If not you got wrong dataset.\n",
    "\n",
    "This is ultra-clunky. Should fix in the future. But it takes names (which is how the BLS files are written) and then will map them into a datatime value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = [14,15,16,17,18,19]\n",
    "\n",
    "empl_time_dict = {}\n",
    "\n",
    "foo = 0\n",
    "\n",
    "for xxx in years:\n",
    "    \n",
    "    \n",
    "    year = int(\"20\" + str(xxx))\n",
    "    \n",
    "    \n",
    "    empl_time_dict[\"year_{0}\".format(year)] = {\"January Employment\":dt.datetime(year,1,1),\n",
    "                 \"February Employment\":dt.datetime(year,2,1),\n",
    "                 \"March Employment\":dt.datetime(year,3,1),\n",
    "                 \"April Employment\":dt.datetime(year,4,1),\n",
    "                 \"May Employment\":dt.datetime(year,5,1),\n",
    "                 \"June Employment\":dt.datetime(year,6,1),\n",
    "                 \"July Employment\":dt.datetime(year,7,1),\n",
    "                 \"August Employment\":dt.datetime(year,8,1),\n",
    "                 \"September Employment\":dt.datetime(year,9,1),\n",
    "                 \"October Employment\":dt.datetime(year,10,1),\n",
    "                 \"November Employment\":dt.datetime(year,11,1),\n",
    "                 \"December Employment\":dt.datetime(year,12,1),}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "clistQ1 = ['Area\\nCode','NAICS','Qtr','January Employment', 'February Employment',\n",
    "       'March Employment', 'Total Quarterly Wages', 'Average Weekly Wage','Own',\"Area Type\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Download\n",
    "\n",
    "This downloads the ``.zip`` files for which we can grab the data. They are all in excell format. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://data.bls.gov/cew/data/files/2014/xls/2014_all_county_high_level.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2014 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2014.extractall(cwd + \"\\\\bls_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://data.bls.gov/cew/data/files/2015/xls/2015_all_county_high_level.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2015 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2015.extractall(cwd + \"\\\\bls_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://data.bls.gov/cew/data/files/2016/xls/2016_all_county_high_level.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2016 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2016.extractall(cwd + \"\\\\bls_files\")\n",
    "\n",
    "url = \"https://data.bls.gov/cew/data/files/2017/xls/2017_all_county_high_level.zip\"\n",
    "# This will read in the annual, single file. It's big, but has all we want...\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "# convert bytes to zip file  \n",
    "bls_q2017 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2017.extractall(cwd + \"\\\\bls_files\")\n",
    "\n",
    "url = \"https://data.bls.gov/cew/data/files/2018/xls/2018_all_county_high_level.zip\"\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "bls_q2018 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2018.extractall(cwd + \"\\\\bls_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://data.bls.gov/cew/data/files/2019/xls/2019_all_county_high_level.zip\"\n",
    "\n",
    "r = requests.get(url) \n",
    "\n",
    "bls_q2019 = zf.ZipFile(io.BytesIO(r.content)) \n",
    "bls_q2019.extractall(cwd + \"\\\\bls_files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<ZipInfo filename='allhlcn191.xlsx' compress_type=deflate external_attr=0x20 file_size=6839716 compress_size=6715278>]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bls_q2019.filelist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "   ### Step 3: Clean and Shape it\n",
    "   \n",
    "   Below is a function that takes in an excell sheet and does what we want to it. Then below we will work through a for loop over all the sheets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def bls_quarter_cat(df,cat,var_name, time_dict):\n",
    "    \n",
    "    # Take only private\n",
    "\n",
    "    df = df[df[\"Own\"] == 5] \n",
    "\n",
    "# Take aggregate\n",
    "\n",
    "    #df = df[df[\"NAICS\"] == 101] # Take goods producing \n",
    "    \n",
    "    df = df[df[\"NAICS\"] == cat] # Take all employment in all sectors\n",
    "\n",
    "# Take only counties \n",
    "    df = df[df[\"Area Type\"] == \"County\"] \n",
    "\n",
    "    df.rename({\"Area\\nCode\": \"GEOFIPS\"},axis = 1, inplace = True)\n",
    "\n",
    "    df[\"GEOFIPS\"] = df[\"GEOFIPS\"].astype(int)\n",
    "\n",
    "    df.set_index(\"GEOFIPS\", inplace = True)\n",
    "\n",
    "    df = df.reindex(trade_data.index.get_level_values(0).unique().astype(int).tolist())\n",
    "    # this part makes sure that we are always getting same counties as in the trade data set\n",
    "    # if we don't do this, things are floating around. The issue I think is for some of the really\n",
    "    # small counties, the employment in the catagory is withehld, so when we grab this stuff,\n",
    "    # the county goes missing\n",
    "\n",
    "    df = df.iloc[:,[13,14,15]].reset_index()\n",
    "    # This grabs only values we want, i.e. the employment for that quarter. So for example,\n",
    "    # in Q1, 13 = January, 14 = Febuary, 15 = March. And so forth for Q2...\n",
    "\n",
    "    df = df.melt(\"GEOFIPS\")\n",
    "\n",
    "    df.replace(time_dict,inplace = True)\n",
    "\n",
    "    df.rename({\"variable\":\"time\", \"value\": var_name, \"GEOFIPS\": \"area_fips\"}, axis = 1, inplace = True)\n",
    "    \n",
    "    df[\"area_fips\"] = df[\"area_fips\"].astype(str)\n",
    "    \n",
    "    df.set_index([\"area_fips\", \"time\"], inplace = True)\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_bls_quarter(excell_sheet, time_dict):\n",
    "\n",
    "    foo = pd.read_excel(excell_sheet, sheet_name = \"US_St_Cn_MSA\")\n",
    "    \n",
    "    naics_cats = [10,101,1021]\n",
    "    \n",
    "    var_name = {10: \"emp_all\", 101:\"emp_gds\", 1021: \"emp_n_gds\"}\n",
    "    \n",
    "    cat_dict = {}\n",
    "    \n",
    "    for xxx in naics_cats:\n",
    "        \n",
    "        foo_df = bls_quarter_cat(foo, xxx, var_name[xxx], time_dict)\n",
    "        \n",
    "        cat_dict[\"cat_{0}\".format(xxx)] = foo_df\n",
    "                \n",
    "    df = cat_dict[\"cat_10\"].merge(cat_dict[\"cat_101\"], left_index = True, right_index = True)\n",
    "    \n",
    "    df = df.merge(cat_dict[\"cat_1021\"], left_index = True, right_index = True)\n",
    "    \n",
    "    #df = df.merge(cat_dict[\"cat_1012\"], left_index = True, right_index = True)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\github\\consumption_and_tradewar\\bls_files\\allhlcn181.xlsx\n"
     ]
    }
   ],
   "source": [
    "root_name = cwd + \"\\\\bls_files\\\\\"\n",
    "\n",
    "root_name = root_name + \"allhlcn18\"\n",
    "\n",
    "file_name = root_name + \"1\" + \".xlsx\"\n",
    "\n",
    "print(file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(file_name, sheet_name = \"US_St_Cn_MSA\")\n",
    "\n",
    "#test = bls_quarter_cat(df,1021,\"retail\", empl_time_dict[\"year_2018\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([  10,  101, 1011, 1012, 1013,  102, 1021, 1022, 1023, 1024, 1025,\n",
       "       1026, 1027, 1029], dtype=int64)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>emp_all</th>\n",
       "      <th>emp_gds</th>\n",
       "      <th>emp_n_gds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>area_fips</th>\n",
       "      <th>time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>10001</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13180.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10003</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>250589.0</td>\n",
       "      <td>24540.0</td>\n",
       "      <td>47832.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10005</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>66832.0</td>\n",
       "      <td>15847.0</td>\n",
       "      <td>15431.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1001</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>8345.0</td>\n",
       "      <td>2253.0</td>\n",
       "      <td>2094.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1003</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>59948.0</td>\n",
       "      <td>8878.0</td>\n",
       "      <td>16878.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9009</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>313566.0</td>\n",
       "      <td>43292.0</td>\n",
       "      <td>66545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9011</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>91876.0</td>\n",
       "      <td>21374.0</td>\n",
       "      <td>20811.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9013</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>26519.0</td>\n",
       "      <td>5176.0</td>\n",
       "      <td>6355.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9015</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>31264.0</td>\n",
       "      <td>7353.0</td>\n",
       "      <td>8306.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9999</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>41307.0</td>\n",
       "      <td>2440.0</td>\n",
       "      <td>8925.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9756 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       emp_all  emp_gds  emp_n_gds\n",
       "area_fips time                                    \n",
       "10001     2018-01-01       0.0      0.0    13180.0\n",
       "10003     2018-01-01  250589.0  24540.0    47832.0\n",
       "10005     2018-01-01   66832.0  15847.0    15431.0\n",
       "1001      2018-01-01    8345.0   2253.0     2094.0\n",
       "1003      2018-01-01   59948.0   8878.0    16878.0\n",
       "...                        ...      ...        ...\n",
       "9009      2018-03-01  313566.0  43292.0    66545.0\n",
       "9011      2018-03-01   91876.0  21374.0    20811.0\n",
       "9013      2018-03-01   26519.0   5176.0     6355.0\n",
       "9015      2018-03-01   31264.0   7353.0     8306.0\n",
       "9999      2018-03-01   41307.0   2440.0     8925.0\n",
       "\n",
       "[9756 rows x 3 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_bls_quarter(file_name, empl_time_dict[\"year_2018\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then given the function above, work through the file list. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame([])\n",
    "\n",
    "############################################################################\n",
    "root_name = cwd + \"\\\\bls_files\\\\\"\n",
    "\n",
    "root_name = root_name + \"allhlcn14\"\n",
    "\n",
    "quarter = [\"1\",\"2\",\"3\",\"4\"]\n",
    "\n",
    "for item in quarter:\n",
    "    \n",
    "    file_name = root_name + item + \".xlsx\"\n",
    "    \n",
    "    df = df.append(clean_bls_quarter(file_name,empl_time_dict[\"year_2014\"]))\n",
    "\n",
    "############################################################################\n",
    "root_name = cwd + \"\\\\bls_files\\\\\"\n",
    "\n",
    "root_name = root_name + \"allhlcn15\"\n",
    "\n",
    "quarter = [\"1\",\"2\",\"3\",\"4\"]\n",
    "\n",
    "for item in quarter:\n",
    "    \n",
    "    file_name = root_name + item + \".xlsx\"\n",
    "    \n",
    "    df = df.append(clean_bls_quarter(file_name,empl_time_dict[\"year_2015\"]))\n",
    "\n",
    "############################################################################\n",
    "\n",
    "root_name = cwd + \"\\\\bls_files\\\\\"\n",
    "\n",
    "root_name = root_name + \"allhlcn16\"\n",
    "\n",
    "quarter = [\"1\",\"2\",\"3\",\"4\"]\n",
    "\n",
    "for item in quarter:\n",
    "    \n",
    "    file_name = root_name + item + \".xlsx\"\n",
    "    \n",
    "    df = df.append(clean_bls_quarter(file_name,empl_time_dict[\"year_2016\"]))\n",
    "    \n",
    "############################################################################\n",
    "\n",
    "root_name = cwd + \"\\\\bls_files\\\\\"\n",
    "\n",
    "root_name = root_name + \"allhlcn17\"\n",
    "\n",
    "quarter = [\"1\",\"2\",\"3\",\"4\"]\n",
    "\n",
    "for item in quarter:\n",
    "    \n",
    "    file_name = root_name + item + \".xlsx\"\n",
    "    \n",
    "    df = df.append(clean_bls_quarter(file_name,empl_time_dict[\"year_2017\"]))\n",
    "    \n",
    "############################################################################  \n",
    "root_name = cwd + \"\\\\bls_files\\\\\"\n",
    "\n",
    "root_name = root_name + \"allhlcn18\"\n",
    "\n",
    "quarter = [\"1\",\"2\",\"3\",\"4\"]\n",
    "\n",
    "for item in quarter:\n",
    "    \n",
    "    file_name = root_name + item + \".xlsx\"\n",
    "    \n",
    "    df = df.append(clean_bls_quarter(file_name,empl_time_dict[\"year_2018\"]))\n",
    "    \n",
    "############################################################################  \n",
    "root_name = cwd + \"\\\\bls_files\\\\\"\n",
    "\n",
    "root_name = root_name + \"allhlcn19\"\n",
    "\n",
    "quarter = [\"1\"]\n",
    "\n",
    "for item in quarter:\n",
    "    \n",
    "    file_name = root_name + item + \".xlsx\"\n",
    "    \n",
    "    df = df.append(clean_bls_quarter(file_name,empl_time_dict[\"year_2019\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then just checksome stuff, reshape, then save for the analysis part. Note how this is working (again clunky), if you want the goods employment, uncomment out that. If you want total employment do the other one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values([\"area_fips\", \"time\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "emp_all      float64\n",
       "emp_gds      float64\n",
       "emp_n_gds    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_employ = trade_data.merge(df, left_index = True, right_index = True, how = \"right\")\n",
    "# This is a place to be mindfull about time period, if we want \n",
    "# do left if you just want to conform with the trade data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_employ.total_employment.fillna(method='bfill', inplace = True)\n",
    "\n",
    "trade_employ.tariff.fillna(method='bfill', inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I'm going to layer in some stuff from the Census. First, bring in the 2010 population and the rural share"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfrural = pd.read_excel(\"https://www2.census.gov/geo/docs/reference/ua/County_Rural_Lookup.xlsx\", skiprows=[0,1,2],\n",
    "                       nrows = 3142)\n",
    "\n",
    "dfrural[\"area_fips\"] = dfrural[\"2015 GEOID\"].astype(int)\n",
    "\n",
    "trade_employ.reset_index(inplace = True)\n",
    "\n",
    "trade_employ[\"area_fips\"] = trade_employ[\"area_fips\"].astype(int)\n",
    "\n",
    "trade_employ = trade_employ.merge(dfrural[[\"area_fips\", \"2010 Census \\nPercent Rural\",\n",
    "                             \"2010 Census Total Population\"]], left_on = \"area_fips\", right_on = \"area_fips\",\n",
    "                                 how = \"left\")\n",
    "\n",
    "trade_employ.set_index([\"area_fips\", \"time\"],inplace = True)\n",
    "\n",
    "trade_employ.rename({\"2010 Census \\nPercent Rural\": \"rural_share\",\n",
    "            \"2010 Census Total Population\": \"2010_population\"}, inplace = True, axis = 1)\n",
    "\n",
    "trade_employ[\"rural_share\"] = 0.01*trade_employ[\"rural_share\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then lets bring in the stuff from the ACS5, so population and income level in 2017."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_api_key = '34e40301bda77077e24c859c6c6c0b721ad73fc7'\n",
    "# This is my api_key\n",
    "\n",
    "c = Census(my_api_key)\n",
    "# This will create an object c which has methods associated with it.\n",
    "# We will see  these below.\n",
    "\n",
    "type(c) \n",
    "# Per the discussion below, try c.tab and see the options. \n",
    "\n",
    "code = (\"NAME\",\"B01001_001E\",\"B19013_001E\") # Same Codes:\n",
    "\n",
    "county_2017 = pd.DataFrame(c.acs5.get(code, \n",
    "                                         {'for': 'county:*'}, year=2017))\n",
    "                                         # Same deal, but we specify county then the wild card\n",
    "                                         # On the example page, there are ways do do this, only by state\n",
    "        \n",
    "county_2017 = county_2017.rename(columns = {\"B01001_001E\":\"2017_population\", \"B19013_001E\":\"2017_income\"})\n",
    "\n",
    "county_2017[\"GEOFIPS\"] = (county_2017[\"state\"] + county_2017[\"county\"]).astype(int)\n",
    "\n",
    "county_2017[\"2017_population\"] = county_2017[\"2017_population\"].astype(float)\n",
    "\n",
    "county_2017[\"2017_income\"] = county_2017[\"2017_income\"].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_2017.set_index([\"GEOFIPS\"], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "trade_employ.reset_index(inplace = True)\n",
    "\n",
    "trade_employ = trade_employ.merge(county_2017[[\"2017_income\",\"2017_population\"]],\n",
    "                                  left_on = \"area_fips\", right_index = True, how = \"left\")\n",
    "\n",
    "#trade_employ.drop(labels = \"index\", axis = 1, inplace = True)\n",
    "\n",
    "trade_employ.set_index([\"area_fips\", \"time\"],inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_exp_pc</th>\n",
       "      <th>china_exp_pc</th>\n",
       "      <th>tariff</th>\n",
       "      <th>emplvl_2017</th>\n",
       "      <th>fips</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>emp_all</th>\n",
       "      <th>emp_gds</th>\n",
       "      <th>emp_n_gds</th>\n",
       "      <th>rural_share</th>\n",
       "      <th>2010_population</th>\n",
       "      <th>2017_income</th>\n",
       "      <th>2017_population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2018-06-01</td>\n",
       "      <td>535.434797</td>\n",
       "      <td>41.752458</td>\n",
       "      <td>0.212037</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>256164.0</td>\n",
       "      <td>26029.0</td>\n",
       "      <td>47449.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-07-01</td>\n",
       "      <td>500.815773</td>\n",
       "      <td>39.350916</td>\n",
       "      <td>0.207430</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>255968.0</td>\n",
       "      <td>26326.0</td>\n",
       "      <td>47232.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-08-01</td>\n",
       "      <td>531.909492</td>\n",
       "      <td>42.688098</td>\n",
       "      <td>0.207430</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>255382.0</td>\n",
       "      <td>26407.0</td>\n",
       "      <td>47307.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-09-01</td>\n",
       "      <td>514.280421</td>\n",
       "      <td>38.330528</td>\n",
       "      <td>0.260085</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>254270.0</td>\n",
       "      <td>26262.0</td>\n",
       "      <td>47359.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>545.881585</td>\n",
       "      <td>39.658499</td>\n",
       "      <td>0.464673</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>257394.0</td>\n",
       "      <td>26398.0</td>\n",
       "      <td>48050.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-11-01</td>\n",
       "      <td>516.246195</td>\n",
       "      <td>37.360160</td>\n",
       "      <td>0.456547</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>259963.0</td>\n",
       "      <td>26256.0</td>\n",
       "      <td>49958.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>499.745029</td>\n",
       "      <td>39.971812</td>\n",
       "      <td>0.456547</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>260304.0</td>\n",
       "      <td>26435.0</td>\n",
       "      <td>50632.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>496.613756</td>\n",
       "      <td>34.459205</td>\n",
       "      <td>0.448531</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>253387.0</td>\n",
       "      <td>25940.0</td>\n",
       "      <td>48260.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-02-01</td>\n",
       "      <td>485.506472</td>\n",
       "      <td>35.747547</td>\n",
       "      <td>0.448519</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>252503.0</td>\n",
       "      <td>25814.0</td>\n",
       "      <td>47848.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>552.995665</td>\n",
       "      <td>47.108656</td>\n",
       "      <td>0.448519</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>254122.0</td>\n",
       "      <td>26245.0</td>\n",
       "      <td>48161.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            total_exp_pc  china_exp_pc    tariff  emplvl_2017   fips  \\\n",
       "time                                                                   \n",
       "2018-06-01    535.434797     41.752458  0.212037       9072.0  10003   \n",
       "2018-07-01    500.815773     39.350916  0.207430       9072.0  10003   \n",
       "2018-08-01    531.909492     42.688098  0.207430       9072.0  10003   \n",
       "2018-09-01    514.280421     38.330528  0.260085       9072.0  10003   \n",
       "2018-10-01    545.881585     39.658499  0.464673       9072.0  10003   \n",
       "2018-11-01    516.246195     37.360160  0.456547       9072.0  10003   \n",
       "2018-12-01    499.745029     39.971812  0.456547       9072.0  10003   \n",
       "2019-01-01    496.613756     34.459205  0.448531       9072.0  10003   \n",
       "2019-02-01    485.506472     35.747547  0.448519       9072.0  10003   \n",
       "2019-03-01    552.995665     47.108656  0.448519       9072.0  10003   \n",
       "\n",
       "            total_employment   emp_all  emp_gds  emp_n_gds  rural_share  \\\n",
       "time                                                                      \n",
       "2018-06-01          249775.0  256164.0  26029.0    47449.0     0.046024   \n",
       "2018-07-01          249775.0  255968.0  26326.0    47232.0     0.046024   \n",
       "2018-08-01          249775.0  255382.0  26407.0    47307.0     0.046024   \n",
       "2018-09-01          249775.0  254270.0  26262.0    47359.0     0.046024   \n",
       "2018-10-01          249775.0  257394.0  26398.0    48050.0     0.046024   \n",
       "2018-11-01          249775.0  259963.0  26256.0    49958.0     0.046024   \n",
       "2018-12-01          249775.0  260304.0  26435.0    50632.0     0.046024   \n",
       "2019-01-01          249775.0  253387.0  25940.0    48260.0     0.046024   \n",
       "2019-02-01          249775.0  252503.0  25814.0    47848.0     0.046024   \n",
       "2019-03-01          249775.0  254122.0  26245.0    48161.0     0.046024   \n",
       "\n",
       "            2010_population  2017_income  2017_population  \n",
       "time                                                       \n",
       "2018-06-01         538479.0      68336.0         555036.0  \n",
       "2018-07-01         538479.0      68336.0         555036.0  \n",
       "2018-08-01         538479.0      68336.0         555036.0  \n",
       "2018-09-01         538479.0      68336.0         555036.0  \n",
       "2018-10-01         538479.0      68336.0         555036.0  \n",
       "2018-11-01         538479.0      68336.0         555036.0  \n",
       "2018-12-01         538479.0      68336.0         555036.0  \n",
       "2019-01-01         538479.0      68336.0         555036.0  \n",
       "2019-02-01         538479.0      68336.0         555036.0  \n",
       "2019-03-01         538479.0      68336.0         555036.0  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_employ.loc[10003].tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = os.getcwd() + \"\\\\data\\\\trade_employment_retail.parquet\"\n",
    "\n",
    "pq.write_table(pa.Table.from_pandas(trade_employ.reset_index()), file_path)\n",
    "\n",
    "#file_path = os.getcwd() + \"\\\\data\\\\trade_employment_all.parquet\"\n",
    "\n",
    "#pq.write_table(pa.Table.from_pandas(trade_employ.reset_index()), file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "total_exp_pc        float64\n",
       "china_exp_pc        float64\n",
       "tariff              float64\n",
       "emplvl_2017         float64\n",
       "fips                 object\n",
       "total_employment    float64\n",
       "emp_all             float64\n",
       "emp_gds             float64\n",
       "emp_n_gds           float64\n",
       "rural_share         float64\n",
       "2010_population     float64\n",
       "2017_income         float64\n",
       "2017_population     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_employ.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_exp_pc</th>\n",
       "      <th>china_exp_pc</th>\n",
       "      <th>tariff</th>\n",
       "      <th>emplvl_2017</th>\n",
       "      <th>fips</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>emp_all</th>\n",
       "      <th>emp_gds</th>\n",
       "      <th>emp_n_gds</th>\n",
       "      <th>rural_share</th>\n",
       "      <th>2010_population</th>\n",
       "      <th>2017_income</th>\n",
       "      <th>2017_population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.211467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>237770.0</td>\n",
       "      <td>23235.0</td>\n",
       "      <td>47358.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2014-02-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.211467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>235894.0</td>\n",
       "      <td>23115.0</td>\n",
       "      <td>46651.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2014-03-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.211467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>237947.0</td>\n",
       "      <td>23535.0</td>\n",
       "      <td>47070.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.211467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>240968.0</td>\n",
       "      <td>23990.0</td>\n",
       "      <td>47375.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2014-05-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.211467</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>243356.0</td>\n",
       "      <td>24490.0</td>\n",
       "      <td>47466.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-11-01</td>\n",
       "      <td>516.246195</td>\n",
       "      <td>37.360160</td>\n",
       "      <td>0.456547</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>259963.0</td>\n",
       "      <td>26256.0</td>\n",
       "      <td>49958.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>499.745029</td>\n",
       "      <td>39.971812</td>\n",
       "      <td>0.456547</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>260304.0</td>\n",
       "      <td>26435.0</td>\n",
       "      <td>50632.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>496.613756</td>\n",
       "      <td>34.459205</td>\n",
       "      <td>0.448531</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>253387.0</td>\n",
       "      <td>25940.0</td>\n",
       "      <td>48260.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-02-01</td>\n",
       "      <td>485.506472</td>\n",
       "      <td>35.747547</td>\n",
       "      <td>0.448519</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>252503.0</td>\n",
       "      <td>25814.0</td>\n",
       "      <td>47848.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-03-01</td>\n",
       "      <td>552.995665</td>\n",
       "      <td>47.108656</td>\n",
       "      <td>0.448519</td>\n",
       "      <td>9072.0</td>\n",
       "      <td>10003</td>\n",
       "      <td>249775.0</td>\n",
       "      <td>254122.0</td>\n",
       "      <td>26245.0</td>\n",
       "      <td>48161.0</td>\n",
       "      <td>0.046024</td>\n",
       "      <td>538479.0</td>\n",
       "      <td>68336.0</td>\n",
       "      <td>555036.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            total_exp_pc  china_exp_pc    tariff  emplvl_2017   fips  \\\n",
       "time                                                                   \n",
       "2014-01-01           NaN           NaN  0.211467          NaN    NaN   \n",
       "2014-02-01           NaN           NaN  0.211467          NaN    NaN   \n",
       "2014-03-01           NaN           NaN  0.211467          NaN    NaN   \n",
       "2014-04-01           NaN           NaN  0.211467          NaN    NaN   \n",
       "2014-05-01           NaN           NaN  0.211467          NaN    NaN   \n",
       "...                  ...           ...       ...          ...    ...   \n",
       "2018-11-01    516.246195     37.360160  0.456547       9072.0  10003   \n",
       "2018-12-01    499.745029     39.971812  0.456547       9072.0  10003   \n",
       "2019-01-01    496.613756     34.459205  0.448531       9072.0  10003   \n",
       "2019-02-01    485.506472     35.747547  0.448519       9072.0  10003   \n",
       "2019-03-01    552.995665     47.108656  0.448519       9072.0  10003   \n",
       "\n",
       "            total_employment   emp_all  emp_gds  emp_n_gds  rural_share  \\\n",
       "time                                                                      \n",
       "2014-01-01          249775.0  237770.0  23235.0    47358.0     0.046024   \n",
       "2014-02-01          249775.0  235894.0  23115.0    46651.0     0.046024   \n",
       "2014-03-01          249775.0  237947.0  23535.0    47070.0     0.046024   \n",
       "2014-04-01          249775.0  240968.0  23990.0    47375.0     0.046024   \n",
       "2014-05-01          249775.0  243356.0  24490.0    47466.0     0.046024   \n",
       "...                      ...       ...      ...        ...          ...   \n",
       "2018-11-01          249775.0  259963.0  26256.0    49958.0     0.046024   \n",
       "2018-12-01          249775.0  260304.0  26435.0    50632.0     0.046024   \n",
       "2019-01-01          249775.0  253387.0  25940.0    48260.0     0.046024   \n",
       "2019-02-01          249775.0  252503.0  25814.0    47848.0     0.046024   \n",
       "2019-03-01          249775.0  254122.0  26245.0    48161.0     0.046024   \n",
       "\n",
       "            2010_population  2017_income  2017_population  \n",
       "time                                                       \n",
       "2014-01-01         538479.0      68336.0         555036.0  \n",
       "2014-02-01         538479.0      68336.0         555036.0  \n",
       "2014-03-01         538479.0      68336.0         555036.0  \n",
       "2014-04-01         538479.0      68336.0         555036.0  \n",
       "2014-05-01         538479.0      68336.0         555036.0  \n",
       "...                     ...          ...              ...  \n",
       "2018-11-01         538479.0      68336.0         555036.0  \n",
       "2018-12-01         538479.0      68336.0         555036.0  \n",
       "2019-01-01         538479.0      68336.0         555036.0  \n",
       "2019-02-01         538479.0      68336.0         555036.0  \n",
       "2019-03-01         538479.0      68336.0         555036.0  \n",
       "\n",
       "[63 rows x 13 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trade_employ.loc[10003]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
